package com.pentajock.docdb.database;

/*
 * Liscense: 	GPL http://www.gnu.org/copyleft/gpl.html
 * By: 		A. Tres Finocchiaro
 * Date:	2007
 * Java:	6.0SE
 */
//
//
import com.pentajock.docdb.Base64.Base64;
import com.pentajock.docdb.Main;
import com.pentajock.docdb.dialogs.Dialogs;
import com.pentajock.docdb.types.DepartmentProperties;
import com.pentajock.docdb.types.Document;
import com.pentajock.docdb.types.DocumentProperties;
import com.pentajock.docdb.types.User;
import com.pentajock.docdb.types.WeightedDocument;
import com.pentajock.docdb.utils.Utils;
import java.security.*;
import java.sql.*;
import java.util.*;
import java.io.File;
//import javax.swing.DefaultListModel;
import java.io.IOException;
import org.hsqldb.Server; // Only used for embedded servers




public class DBController {
	static private Connection con;
	static private Statement s;
	static private Server embeddedServer = null; // Only used if a real server can't be found
	static private boolean isConnected = false;
        
	/*
	 * Cannot instantiate.
	 */
	private DBController() {
	}
	
	static private final String DOC_QUERY_COMPONENTS = "DocumentID, DepartmentName, DocumentName, Extension, Category, EffectiveDate, UpdateInterval, DocumentPath, IsApproved, NewDocumentName, Assignee";
	
	static private PreparedStatement isFirstLoginStatement;
	static private final String IS_FIRST_LOGIN_STATEMENT = "SELECT UserPassword FROM Users WHERE UserName = ?;";
	static public boolean isFirstLogin(String userName) throws SQLException {
		isFirstLoginStatement.setString(1, userName);
		ResultSet rs = isFirstLoginStatement.executeQuery();
		if (rs != null && rs.next()) 
			return rs.getString(1) == null;
		return false;
	}
	
	
	static private PreparedStatement checkPasswordStatement;
	static private final String CHECK_PASSWORD_STATEMENT = "SELECT UserName FROM Users WHERE UserName = ? AND UserPassword = ?;";
	static public boolean checkPassword(String userName, String password) throws SQLException {
		checkPasswordStatement.setString(1, userName);
		checkPasswordStatement.setString(2, encryptString(password));
		ResultSet rs = checkPasswordStatement.executeQuery();
		if (rs.next())
			return rs.getString(1).equalsIgnoreCase(userName);
		return false;
	}
	
	static public List<String> getExtensions() {
		try {
			List<String> docExtensions = new ArrayList<String>();
			ResultSet rs = query("SELECT Extension FROM Extensions;");
			while(rs.next()) {
				docExtensions.add(rs.getString(1));
			}
			return docExtensions;
		}
		catch(SQLException ex) {
			Main.printStackTrace(ex, "Error occurred trying to get extension names");
		}
		return null;
	}
	
	/* Can't set up the prepared statement at database connection for this one. */
	static private Object getExtensionSingleProperty(String property, Object o) {
		String ext;
		File f;
		if (o instanceof File)
			f = (File)o;
		else if (o instanceof String)
			f = null;
		else 
			return null;
		ext = Utils.getExtension(o);
		try {
			ResultSet rs = null;
			if (!ext.equalsIgnoreCase("xml")) {
				PreparedStatement pstmt = con.prepareStatement("SELECT " + property + " FROM Extensions WHERE Extension = ?;");
				pstmt.setString(1, ext);
				rs = pstmt.executeQuery();
			}
			else if (f != null && f.exists()) {
				String xmlType = Utils.getXMLDocumentType(f);
				String activeXCall = Utils.getSubject(xmlType) + ".Application";
				Main.printDebug(activeXCall);
				PreparedStatement pstmt = con.prepareStatement("SELECT " + property + " FROM Extensions WHERE ActiveX = ?;");
				pstmt.setString(1, activeXCall);
				rs = pstmt.executeQuery();
			}
			return rs != null && rs.next() ? rs.getObject(1) : null;
		}
		catch (SQLException sql) {
			Main.printStackTrace(sql, "SQL error occurred trying to get extension property " + ext);
		}
		catch (Exception e) {
			Main.printStackTrace(e, "Exception error occurred trying to get extension property " + ext);
		}
		return null;
	}
	
	static public boolean supportsHeader(Object o) {
		o = getExtensionSingleProperty("SupportsHeader", o);
		return o!=null?(Boolean)o:false;
	}
	
	static public boolean supportsCreateNew(Object o) {
		o = getExtensionSingleProperty("SupportsCreateNew", o);
		return o!=null?(Boolean)o:false;
	}
	
	static public boolean supportsBuiltInProperties(Object o) {
		o = getExtensionSingleProperty("SupportsProperties", o);
		return o!=null?(Boolean)o:false;
	}
	
	static public boolean supportsCustomProperties(Object o) {
		o = getExtensionSingleProperty("SupportsCustomProperties", o);
		return o!=null?(Boolean)o:false;
	}

	static public String getActiveXCall(Object o) {
		return (String)getExtensionSingleProperty("ActiveX", o);
	}
	
	static public String getDocumentCall(Object o) {
		return (String)getExtensionSingleProperty("DocumentCall", o);
	}

	static public boolean changePassword(String userName, String originalPassword, String newPassword) {
		if(newPassword == null)
			return false;
		
		try {
			return (checkPassword(userName, originalPassword) && setPassword(userName, newPassword));
		}
		catch(SQLException ex) {
			Main.printStackTrace(ex, "Error changing password for user \"" + userName + "\"");
		}
		return false;
	}
	
	static public boolean setFirstPassword(String userName, String password) {
		try {
			if(isFirstLogin(userName)) {
				return setPassword(userName, password);
			}
		}
		catch(SQLException ex) {
			Main.printStackTrace(ex, "Error occurred setting first password.");
		}
		return false;
	}
	
	/* Change password for a user other than yourself.  Protected, and reliant on a passphrase. */
	//static protected boolean
        static public boolean changeUserPassword(String userName, String password, String passPhrase) {
		if (passPhrase.equals("Passphrase"))
			return setPassword(userName, password);
		return false;
	}
	
	static public List<String> getFileServers() {
		return getFileServers(false);
	}
	
	static public List<String> getFileServers(boolean onlyEnabledServers) {
		try {
			List<String> fileServers = new ArrayList<String>();
			ResultSet rs;
			if (onlyEnabledServers)
				rs = query("SELECT URL FROM FileServers WHERE IsEnabled = 'true';");
			else
				rs = query("SELECT URL FROM FileServers;");
			while(rs.next()) {
				fileServers.add(rs.getString(1));
			}
			return fileServers;
		}
		catch(SQLException ex) {
			Main.printStackTrace(ex, "Error occurred trying to get list of file servers");
		}
		return null;
	}
	
	static private PreparedStatement addFileServerStatement;
	static private final String ADD_FILESERVER_STATEMENT = "INSERT INTO FileServers (URL, IsEnabled) VALUES (?, ?);";
	static public boolean addFileServer(String serverAddress, boolean isEnabled) {
		try {
			addFileServerStatement.setString(1, serverAddress);
			addFileServerStatement.setBoolean(2, isEnabled);
			return (addFileServerStatement.executeUpdate() == 1);
		}
		catch(SQLException ex) {
			Main.printStackTrace(ex, "Error adding FileServer " + serverAddress + " due to SQL error");
		}
		return false;
	}
	
	static private PreparedStatement removeFileServerStatement;
	static private final String REMOVE_FILESERVER_STATEMENT = "DELETE FROM FileServers WHERE URL = ?;";
	static public boolean addFileServer(String serverAddress) {
		try {
			removeFileServerStatement.setString(1, serverAddress);
			return (removeFileServerStatement.executeUpdate() == 1);
		}
		catch(SQLException ex) {
			Main.printStackTrace(ex, "Error removing FileServer " + serverAddress + " due to SQL error");
		}
		return false;
	}
	
	
	static private PreparedStatement addExtensionStatement;
	static private final String ADD_EXTENSION_STATEMENT = "INSERT INTO Extensions (Extension, ActiveX, DocumentCall, DefaultCategory, SupportsProperties, SupportsHeader, SupportsCustomProperties, SupportsCreateNew) VALUES (?, ?, ?, ?, ?, ?, ?, ?);";
	static public boolean addExtension(Object[] extProp) {
		try {
			addExtensionStatement.setString(1, (String)extProp[0]);
			addExtensionStatement.setString(2, (String)extProp[1]);
			addExtensionStatement.setString(3, (String)extProp[2]);
			addExtensionStatement.setString(4, (String)extProp[3]);
			addExtensionStatement.setBoolean(5, (Boolean)extProp[4]);
			addExtensionStatement.setBoolean(6, (Boolean)extProp[5]);
			addExtensionStatement.setBoolean(7, (Boolean)extProp[6]);
			addExtensionStatement.setBoolean(8, (Boolean)extProp[7]);
			return (addExtensionStatement.executeUpdate() == 1);
		}
		catch(SQLException ex) {
			Main.printStackTrace(ex, "Error adding extension " + (String)extProp[0] + " due to SQL error");
		}
		return false;
	}

	/*
	 * Creates a backup of the current documents
	 */     
	static public void backupDocuments() throws SQLException {
		Main.printDebug("Backing up documents table");
		String dropOldDocs = "DROP TABLE IF EXISTS Documents_Old;";
		String dropOldProps = "DROP TABLE IF EXISTS DocumentProperties_Old;";
		String backupDocs = "SELECT * INTO Documents_Old FROM Documents;";
		String backupProps = "SELECT * INTO DocumentProperties_Old FROM DocumentProperties;";
		con.prepareStatement(dropOldProps).executeUpdate();
		con.prepareStatement(dropOldDocs).executeUpdate();
		con.prepareStatement(backupProps).executeUpdate();
		con.prepareStatement(backupDocs).executeUpdate();
	}
	
	static public void restoreDocuments() throws SQLException {
		clearDocuments();
		Main.printDebug("Restoring documents + properties tables");
		String dropProps = "DROP TABLE IF EXISTS DocumentProperties;";
		String dropDocs = "DROP TABLE IF EXISTS Documents;";
		String restoreProps = "SELECT * INTO DocumentProperties FROM DocumentProperties_Old;";
		String restoreDocs = "SELECT * INTO Documents FROM Documents_Old;";
		con.prepareStatement(dropProps).executeUpdate();
		con.prepareStatement(dropDocs).executeUpdate();
		con.prepareStatement(restoreProps).executeUpdate();
		con.prepareStatement(restoreDocs).executeUpdate();
	}
	
	static public void clearDocuments() throws SQLException {
		Main.printDebug("Clearing documents table");
		String clearProps = "DELETE FROM DocumentProperties WHERE DocumentID LIKE '%';";
		String clearDocs = "DELETE FROM Documents WHERE DocumentID LIKE '%';";
		con.prepareStatement(clearProps).executeUpdate();	// Clear props first due to restraints
		con.prepareStatement(clearDocs).executeUpdate();
	}
	
	static private PreparedStatement updateExtensionStatement;
	static private final String UPDATE_EXTENSION_STATEMENT = "UPDATE Extensions SET ActiveX = ?, DocumentCall = ?, DefaultCategory = ?, SupportsProperties = ?, SupportsCustomProperties = ?, SupportsHeader = ?, SupportsCreateNew = ? WHERE Extension = ?;";
	static public boolean updateExtension(Object[] extProp) {
		try {
			updateExtensionStatement.setString(1, (String)extProp[1]);
			updateExtensionStatement.setString(2, (String)extProp[2]);
			updateExtensionStatement.setString(3, (String)extProp[3]);
			updateExtensionStatement.setBoolean(4, (Boolean)extProp[4]);
			updateExtensionStatement.setBoolean(5, (Boolean)extProp[5]);
			updateExtensionStatement.setBoolean(6, (Boolean)extProp[6]);
			updateExtensionStatement.setBoolean(7, (Boolean)extProp[7]);
			updateExtensionStatement.setString(8, (String)extProp[0]);
			return (updateExtensionStatement.executeUpdate() == 1);	
		}
		catch(SQLException ex) {
			Main.printStackTrace(ex, "Error updating extension " + (String)extProp[0] + " due to SQL error");
		}
		return false;
	}
	
	static private PreparedStatement deleteExtensionStatement;
	static private final String DELETE_EXTENSION_STATEMENT = "DELETE FROM Extensions WHERE Extension = ?;";
	static public boolean deleteExtension(String ext) {
		try {
			deleteExtensionStatement.setString(1, ext);
			return (deleteExtensionStatement.executeUpdate() == 1);	
		}
		catch(SQLException ex) {
			Main.printStackTrace(ex, "Error deleting extension " + ext + " due to SQL error");
		}
		return false;
	}
	
	static private PreparedStatement updateUserStatement;
	static private final String UPDATE_USER_STATEMENT = "UPDATE Users SET UserName = ?, FirstName = ?, LastName = ?, DepartmentName = ?, IsDepartmentAdmin = ?, IsDatabaseAdmin = ? WHERE UserName = ?;";
	static public boolean updateUser(User u) throws SQLException {
		updateUserStatement.setString(1, u.getUserName());
		updateUserStatement.setString(2, u.getFirstName());
		updateUserStatement.setString(3, u.getLastName());
		updateUserStatement.setString(4, u.getDepartmentName());
		updateUserStatement.setBoolean(5, u.isSupervisor());
		updateUserStatement.setBoolean(6, u.isAdmin());
		updateUserStatement.setString(7, u.getOldUserName());
		return (updateUserStatement.executeUpdate() == 1);
	}
	
	static private PreparedStatement addUserStatement;
	static private final String ADD_USER_STATEMENT = "INSERT INTO Users (UserName, FirstName, LastName, DepartmentName, IsDepartmentAdmin, IsDatabaseAdmin) VALUES (?, ?, ?, ?, ?, ?);";
	static public boolean addUser(User u) throws SQLException {
		addUserStatement.setString(1, u.getUserName());
		addUserStatement.setString(2, u.getFirstName());
		addUserStatement.setString(3, u.getLastName());
		addUserStatement.setString(4, u.getDepartmentName());
		addUserStatement.setBoolean(5, u.isSupervisor());
		addUserStatement.setBoolean(6, u.isAdmin());
		return (addUserStatement.executeUpdate() == 1);
	}
	
	static private PreparedStatement deleteUserStatement;
	static private final String DELETE_USER_STATEMENT = "DELETE FROM Users WHERE UserName = ? and DepartmentName = ?;";
	static public boolean deleteUser(User u) {
		try {
			deleteUserStatement.setString(1, u.getUserName());
			deleteUserStatement.setString(2, u.getDepartmentName());
			return (deleteUserStatement.executeUpdate() == 1);
		}
		catch(SQLException ex) {
			Main.printStackTrace(ex, "Error deleting user " + u.getUserName() + " due to SQL error");
		}
		return false;
	}
	
	static public boolean deleteDocument(Document d) {
		PreparedStatement pstmt;
		try {
			pstmt = con.prepareStatement("DELETE FROM DocumentProperties WHERE DocumentID = ?;");
			pstmt.setInt(1, d.getDocumentID());
			pstmt.executeUpdate();
		}
		catch (Exception e) {
			Main.printStackTrace(e, "Error deleting Document properties... Continuing.");
		}
		try {
			pstmt = con.prepareStatement("DELETE FROM Documents WHERE DocumentID = ?;");
			pstmt.setInt(1, d.getDocumentID());
			return (pstmt.executeUpdate() == 1);
		}
		catch(SQLException sql) {
			Main.printStackTrace(sql, "Error deleting document " + d.getDocumentName() + " due to SQL error");
		}
		return false;
	}
	
	
	static private boolean setPassword(String userName, String password) {
		try {
			String encryptedPass = encryptString(password);
			if (encryptedPass != null) {
				PreparedStatement pstmt = con.prepareStatement("UPDATE Users SET UserPassword = ? WHERE UserName = ?;");
				pstmt.setString(1, encryptedPass);
				pstmt.setString(2, userName);
				return (pstmt.executeUpdate() == 1);
			}
		}
		catch (SQLException ex) {
			Main.printStackTrace(ex, "Error occurred setting password.");
		}
		return false;
	}
	
	/*static public boolean changeName(String userName, String firstName, String lastName) {		
		try {
			return update("UPDATE Users SET FirstName = '" + firstName + "', LastName = '" + lastName + "' WHERE UserName = '" + userName + "';") == 1;
		}
		catch(SQLException ex) {
			Main.printStackTrace(ex, "Error changing name for user \"" + userName + "\"");
		}
		return false;
	}*/
	
	static public String getDocumentRoot(String operatingSystemName) {
		try {
			ResultSet rs = queryQuick("SELECT DocumentRoot FROM OperatingSystems WHERE OperatingSystemName = SUBSTRING('" + operatingSystemName + "', 1, LENGTH(OperatingSystemName));");
			if(rs != null) {
				return rs.getString(1);
			}
		}
		catch(SQLException ex) {
			Main.printStackTrace(ex, "Error occurred trying to get document root for: " + operatingSystemName);
		}
		return null;
	}
	
	static public int getDocumentCount() throws Exception {
		return queryQuick("SELECT COUNT(*) FROM DocumentProperties;").getInt(1); 
	}
	
	static public int getDocumentPropertyCount() throws Exception {
		return queryQuick("SELECT COUNT(*) FROM Documents;").getInt(1); 
	}
	
	static public String getUserRoot(String operatingSystemName) {
		try {
			ResultSet rs = queryQuick("SELECT UserRoot FROM OperatingSystems WHERE OperatingSystemName = SUBSTRING('" + operatingSystemName + "', 1, LENGTH(OperatingSystemName));");
			if(rs != null) {
				return rs.getString(1);
			}
		}
		catch(SQLException ex) {
			Main.printStackTrace(ex, "Error occurred trying to get user root for: " + operatingSystemName);
		}
		return null;
	}
	
	static public String getUserDepartment(String user) {
		try {
			PreparedStatement pstmt = con.prepareStatement("SELECT DepartmentName FROM Users WHERE UserName = ?;");
			pstmt.setString(1, user);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) 
				return rs.getString(1);
		}
		catch(SQLException ex) {
			Main.printStackTrace(ex, "Error occurred trying to get department name for: " + user);
		}
		return null;
	}
	
	static public boolean addDocument(Document d, User u) {
		return addDocument(d, u.getUserName());
	}
	
	/* 
	 * Adds the user approved document to the database
	 *
	 * *** Should populate properties into document and load to custom, 
	 *     but doesn't right now
	 */
	
	private static PreparedStatement addDocumentStatement;
	private static String ADD_DOCUMENT_STATEMENT = "INSERT INTO Documents (DepartmentName, DocumentName, Extension, Category, EffectiveDate, UpdateInterval, DocumentPath, IsApproved, NewDocumentName) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);";
	static public boolean addDocument(Document d, String userName) {
		try {
			addDocumentStatement.setString(1, d.getDepartmentName());
			addDocumentStatement.setString(2, d.getDocumentName());
			addDocumentStatement.setString(3, d.getExtension());
			addDocumentStatement.setString(4, d.getCategory());
			addDocumentStatement.setDate(5, d.getSqlDate());
			addDocumentStatement.setInt(6, d.getUpdateInterval());
			addDocumentStatement.setString(7, d.getPath());
			addDocumentStatement.setBoolean(8, d.isApproved());
			addDocumentStatement.setString(9, d.getNewDocumentName());
			if (addDocumentStatement.executeUpdate() == 1) {
				Main.printDebug("Document added to database... attempting to add Document properties...&");
				boolean b = addDocumentProperties(d, userName);
				Main.printDebug(b?"Success":"Failed");
				return b;
			}
		}
		catch (SQLException e) {
			Main.printStackTrace(e, "Error adding new document to db");
		}
		return false;
	}
	
	
	/*
	 * Deletes all rows in the documents table.  Requires properties to be gone first, due to
	 * restraints
	 *
	 * Please, Don't call this unless you're repopulating. :)
	 */
	private static PreparedStatement removeAllDocumentsStatement;
	private static String REMOVE_ALL_DOCUMENTS_STATEMENT = "DELETE FROM Documents";
	static public int removeAllDocuments() throws SQLException {
		return removeAllDocumentsStatement.executeUpdate();
	}
	
	/*
	 * Deletes all rows in the document properties table.  Needs to be
	 * called before removeAllDocuments
	 */
	private static PreparedStatement removeAllDocumentPropertiesStatement;
	private static String REMOVE_ALL_DOCUMENT_PROPERTIES_STATEMENT = "DELETE FROM DocumentProperties";
	static public int removeAllDocumentProperties() throws SQLException {
		return removeAllDocumentPropertiesStatement.executeUpdate();
	}
	
	/* 
	 * Adds the raw documents to the database, such as from a file scan.
	 * Note, only properties in the documents will be added.  It does not
	 * do any intelligent guessing, such as addDocument(Document, User)
	 */
	 static public int addDocuments(Set<WeightedDocument> allDocs) { //throws SQLException {
		int counter = 0;
		for (Document d : allDocs) {
			try {
				addDocumentStatement.setString(1, d.getDepartmentName());
				addDocumentStatement.setString(2, d.getDocumentName());
				addDocumentStatement.setString(3, d.getExtension());
				addDocumentStatement.setString(4, d.getCategory());
				addDocumentStatement.setDate(5, d.getSqlDate());
				addDocumentStatement.setInt(6, d.getUpdateInterval());
				addDocumentStatement.setString(7, d.getPath());
				addDocumentStatement.setBoolean(8, d.isApproved());
				addDocumentStatement.setString(9, d.getNewDocumentName());
				if (addDocumentStatement.executeUpdate() == 1) {
					addDocumentProperties(d);
				}
				counter++;
			}
			catch (SQLException e) {
				Main.printStackTrace(e);
				//Dialogs.showError(e, d, "Error adding document to database");
			}
		}
		
		return counter;
	 }
	
	/* 
	 * Adds the raw document properties to the database
	 * Note, only properties in the documents will be added.  It does not
	 * do any intelligent guessing.
	 */ 
	static private void addDocumentProperties(Document d) throws SQLException {
		int id = docQuery(d).getDocumentID();
		for (String propertyName : d.getPropertyNames()) {
			String propertyValue = d.getProperty(propertyName);
			if (propertyValue == null || propertyValue.trim().equals(""))
				continue;
			addDocumentPropertiesStatement.setInt(1, id);
			addDocumentPropertiesStatement.setString(2, d.getDepartmentName());
			addDocumentPropertiesStatement.setString(3, propertyName);
			addDocumentPropertiesStatement.setString(4, propertyValue);
			addDocumentPropertiesStatement.executeUpdate();
		}
			
	}
	 
	/* Renames the document in the database based on a file */
	static public boolean renameDocument(Document d, File newFile) {
		return renameDocument(d, Utils.getSubject(newFile), false);
	}
	
	/* 
	 * Renames the document in the database 
	 * based on a string, and can force a 
	 * finalUpdate, if the stored file's name
	 * actually changes
	 */
	static public boolean renameDocument(Document d, String newName, boolean finalUpdate) {
		try {
			//String newName = Utils.getSubject(newFile);
			PreparedStatement p1 = con.prepareStatement("UPDATE Documents SET NewDocumentName = ? WHERE DocumentID = ?;");
			PreparedStatement p2 = con.prepareStatement("UPDATE DocumentProperties SET PropertyValue = ? WHERE DocumentID = ? AND PropertyName = ?;");
			PreparedStatement p3 = con.prepareStatement("UPDATE Documents SET DocumentName = ? WHERE DocumentID = ?;");
			p1.setString(1, finalUpdate?null:newName);
			p1.setInt(2, d.getDocumentID());
			p2.setString(1, newName);
			p2.setInt(2, d.getDocumentID());
			p2.setString(3, "Subject");
			if (finalUpdate) {
				p3.setString(1, newName);
				p3.setInt(2, d.getDocumentID());
				return (p1.executeUpdate() + p2.executeUpdate() + p3.executeUpdate() == 3);
			}
			return (p1.executeUpdate() + p2.executeUpdate() == 2);
		}
		catch (SQLException e) {
			Main.printStackTrace(e, "Error renaming document in db");
		}
		return false;
	}
	
	/* Updates the document in the database */
	static public boolean updateDocument(Document d, User u) {
		try {
			PreparedStatement p1 = con.prepareStatement("UPDATE Documents SET DepartmentName = ?, DocumentName = ?, Extension = ?, Category = ?, UpdateInterval = ?, DocumentPath = ?, IsApproved = ?, NewDocumentName = ?, Assignee = ? WHERE DocumentID = ?;");
			p1.setString(1, d.getDepartmentName());
			p1.setString(2, d.getDocumentName());
			p1.setString(3, d.getExtension());
			p1.setString(4, d.getCategory());
			p1.setInt(5, d.getUpdateInterval());
			p1.setString(6, d.getPath());
			p1.setBoolean(7, d.isApproved());
			p1.setString(8, d.getNewDocumentName());
			p1.setString(9, null);
			p1.setInt(10, d.getDocumentID());
			
			if (p1.executeUpdate() != 1) return false;
			String[] vals = Utils.getConstants(d, u);
			String[] props = {"Updated By", "Subject", "Author", "Effective Date", "Review Date", "Department", "Category", "Company", "Manager"};
			boolean success = true;
			for (int i = 0; i < vals.length; i++) {
				PreparedStatement p2 = con.prepareStatement("UPDATE DocumentProperties SET PropertyValue = ? WHERE DocumentID = ? AND PropertyName = ?;");
				p2.setString(1, vals[i]);
				p2.setInt(2, d.getDocumentID());
				p2.setString(3, props[i]);
				if (p2.executeUpdate() != 1) {
					success = false;
					//break;
				}
			}
			return success;
		}
		catch (SQLException e) {
			Main.printStackTrace(e, "Error updating document in db");
		}
		return false;
	}
	
	
	/*
	 * Convenience method for addProperties(Document, String)
	 */
	static private boolean addDocumentProperties(Document d, User u) {
		return addDocumentProperties(d, u.getUserName());
	}
	
	/*
	 * Adds Document properties to the database
	 * in table DOCUMENTPROPERTIES based on the document
	 * info, and the user's info.  userName is needed to
	 * help get constants such as "Created By", etc.
	 */
	static private PreparedStatement addDocumentPropertiesStatement;
	static private String ADD_DOCUMENT_PROPERTIES_STATEMENT = "INSERT INTO DocumentProperties (DocumentID, DepartmentName, PropertyName, PropertyValue) VALUES (?, ?, ?, ?);";
	static private boolean addDocumentProperties(Document c, String userName) {
		try {
			Document d = docQuery(c);
			if (d == null) return false;
			String departmentName = getUserDepartment(userName);
			String docProp[] = {"Updated By", "Subject", "Author", "Effective Date", "Review Date", "Department", "Category", "Company", "Manager"};
			String docVal[] = Utils.getConstants(d, getUser(userName));
			/** Something is wrong in here */
			Main.printDebug("Adding to DocumentProperties: &");
			for (int i = 0; i < docProp.length; i++) {
				addDocumentPropertiesStatement.setInt(1, d.getDocumentID());
				addDocumentPropertiesStatement.setString(2, d.getDepartmentName());
				addDocumentPropertiesStatement.setString(3, docProp[i]);
				addDocumentPropertiesStatement.setString(4, docVal[i]);
				addDocumentPropertiesStatement.executeUpdate();
				Main.printDebug(docProp[i] + ", &");
			}
			Main.printDebug("... Complete.");
			return true;
		}
		catch (SQLException e) {
			Main.printStackTrace(e, "Error adding document properties to db");
		}
		return false;
	}
	
	static public List<String> getEnabledCategories() {
		try {
			List<String> resultElements = new ArrayList<String>();
			ResultSet rs = query("SELECT CategoryName FROM Categories WHERE IsEnabled = 'true';");
			while(rs.next()) { 
				resultElements.add(rs.getString(1));
			}
			return resultElements;
		}
		catch(SQLException ex) {
			Main.printStackTrace(ex, "Error occurred trying to get Categories");
		}
		return null;
	}
	
	/*
	 * Return a default interval for the specified category.
	 * It's important to use -2 in error, because -1 is a valid value
	 */
	static public int getDefaultInterval(String category) {
		try {
			ResultSet rs = query("SELECT DefaultInterval FROM Categories WHERE CategoryName = '" + category + "';");
			while(rs.next()) { 
				return rs.getInt(1);
			}
		}
		catch(SQLException ex) {
			Main.printStackTrace(ex, "Error occurred trying to get Categories");
		}
		return -2;
	}
	
	static public String getDefaultCategory(Object o) {
		try {
			String category = (String)getExtensionSingleProperty("DefaultCategory", o);
			if (category == null)
				throw new Exception("Null Category Exception");
			return category;
		}
		catch (Exception e) {
			Main.printStackTrace(e, "Error getting default category for " + o.toString() + "  from Extensions");
		}
		try {
			String category = getCategories().get(0);
			if (category == null)
				throw new Exception("Null Category Exception");
			return category;
		}
		catch (Exception n) {
			Main.printStackTrace(n, "No categories found");
		}
		// Could be implimented better
		return "Documentation";
	}
	
	static public List<String> getCategories() {
		try {
			List<String> resultElements = new ArrayList<String>();
			ResultSet rs = query("SELECT CategoryName FROM Categories;");
			while(rs.next()) { 
				resultElements.add(rs.getString(1));
			}
			return resultElements;
		}
		catch(SQLException ex) {
			Main.printStackTrace(ex, "Error occurred trying to get Categories");
		}
		return null;
	}
	
	static private PreparedStatement deleteCategoryStatement;
	static private final String DELETE_CATEGORY_STATEMENT = "DELETE FROM Categories WHERE CategoryName = ?;";
	static public boolean deleteCategory(String category) {
		try {
			if (queryQuick("SELECT COUNT(*) FROM Categories;").getInt(1) < 2)
				return false;
			deleteCategoryStatement.setString(1, category);
			return deleteCategoryStatement.executeUpdate() == 1;
			//PreparedStatement pstmt = con.prepareStatement("DELETE FROM Categories WHERE CategoryName = ?;");
			//pstmt.setString(1, category);
			//return pstmt.executeUpdate() == 1;
		}
		catch(SQLException ex) {
			Main.printStackTrace(ex, "Error occurred trying to delete category");
		}
		return false;
	}
	
	static private PreparedStatement addCategoryStatement;
	static private final String ADD_CATEGORY_STATEMENT = "INSERT INTO Categories (CategoryName, DefaultInterval, IsExpirable, IsEnabled) VALUES(?, '-2', 'false', 'false');";
	static public boolean addCategory(String category) {
		if (category == null)
			return false;
		try {
			addCategoryStatement.setString(1, category);
			return addCategoryStatement.executeUpdate() == 1;
		}
		catch(SQLException ex) {
			Main.printStackTrace(ex, "Error occurred trying to add category");
		}
		return false;
	}
	
	/* 
	 * Updates the category with the appropriate array of values
	 * This array is a strict size (like extensions) and you must reffer
	 * to Dialogs.categoryDialog if changes are made to table structure
	 */
	static private PreparedStatement updateCategoryStatement;
	static private final String UPDATE_CATEGORY_STATEMENT = "UPDATE Categories SET CategoryName = ?, DefaultInterval = ?, IsExpirable = ?, IsEnabled = ? WHERE CategoryName = ?;";
	static public boolean updateCategory(Object[] values) {
		try {
			updateCategoryStatement.setString(1, (String)values[0]);
			updateCategoryStatement.setInt(2, Integer.parseInt((String)values[1]));
			updateCategoryStatement.setBoolean(3, (Boolean)values[2]);
			updateCategoryStatement.setBoolean(4, (Boolean)values[3]);
			updateCategoryStatement.setString(5, (String)values[0]);
			return updateCategoryStatement.executeUpdate() == 1;
		}
		catch(SQLException sql) {
			Main.printStackTrace(sql, "Error occurred trying to set category property");
		}
		catch(NumberFormatException nfe) {
			Main.printStackTrace(nfe, "Invalid integer value " + values[1] + ", record not updated");
		}
		return false;
	}
	
	/*
	 * Sets wheter or not to use this
	 * category in the application
	 */
	//static public boolean setCategoryIsEnabled(String category, boolean value) {
	//	return setCategoryProperty(category, "IsEnabled", value);
	//}
	
	/*
	 * Sets wheter or not documents
	 * of this type will expire and need
	 * updating
	 */
	//static public boolean setCategoryIsExpirable(String category, boolean value) {
	//	return setCategoryProperty(category, "IsExpirable", value);
	//}
	
	
	/* 
	 * Used below by getCategoryIsEnabled(String),
	 * getCategoryDefaultInterval(String) and 
	 * getCategoryIsExpirable(String)
	 */
	static private Object getCategoryProperty(String category, String component) {
		try {
			PreparedStatement pstmt = con.prepareStatement("SELECT " + component + " FROM Categories WHERE CategoryName = ?;");
			pstmt.setString(1, category);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) return rs.getObject(1);
		}
		catch(SQLException sql) {
			Main.printStackTrace(sql, "Error occurred trying to get category component");
		}
		return null;
	}
	
	/*
	 * The default update-interval for this category.  For example, 
	 * documentation or collaboration may never need to expire, where as
	 * as user procedure expires quite often.
	 *
	 * This is mainly used for the repopulation of the database, as these 
	 * values can be changed for each document later on.
	 */
	static public int getCategoryDefaultInterval(String category) {
		Integer i = (Integer)getCategoryProperty(category, "DefaultInterval");
		return i==null?-2:i.intValue();
	}
	
	
	/*
	 * Determine whether or not to use this
	 * category in the application
	 */
	static public boolean getCategoryIsEnabled(String category) {
		Boolean b = (Boolean)getCategoryProperty(category, "IsEnabled");
		return b==null?false:b;
	}
	
	/*
	 * Determines whether or not documents 
	 * of this type can expire
	 */
	static public boolean getCategoryIsExpirable(String category) {
		Boolean b = (Boolean)getCategoryProperty(category, "IsExpirable");
		return b==null?false:b;
	}
	
	/* 
	 * Searches for documents 
	 * based on keywords and 
	 * returns the results 
	 */
	static public List<Document> searchDocuments(Set<String> wordSet) {
		List<Document> foundDocuments = new ArrayList<Document>();
		
		/* 
		 * NOTE:  TRUE and FALSE are used to safely terminate statements w/o 
		 * modifying results
		 */
		
		String likeQuery = "";
		String omitQuery = "";
		
		List<String> omitList = new ArrayList<String>();
		
		/*
		 * Isolate "-" remove wildcard
		 */
		for (String s : wordSet) {
			if (!s.startsWith("-")) {
				likeQuery += "DocumentName LIKE ? OR ";
			}
			else {
				omitQuery += "DocumentName != ? AND ";
				omitList.add(s.replaceAll("^\\-+", ""));
				wordSet.remove(s);
			}
		}
				
		if (!likeQuery.equals(""))
			likeQuery += "FALSE";
		else
			likeQuery += "TRUE";
		
		omitQuery += "TRUE";

		String fullQuery = "(" + likeQuery + ") AND (" + omitQuery + ")";
		Main.printDebug("Executing: " + fullQuery);
		try {
			PreparedStatement pstmt = con.prepareStatement("SELECT " + DOC_QUERY_COMPONENTS + " FROM Documents WHERE " + fullQuery + ";");
			int i = 1;
			for (String w : wordSet) {
				pstmt.setString(i++,"%" + w + "%");
			}
			
			for (String w : omitList) {
				pstmt.setString(i++,"%" + w + "%");
			}
			
			ResultSet rs;
			rs = pstmt.executeQuery();
			while(rs.next())
				foundDocuments.add(constructDocument(rs));
			for(Document d : foundDocuments)
				d.setProperties(getDocumentProperties(d.getDocumentID()));
		}
		catch (SQLException sql) {
			Main.printStackTrace(sql, "Error retreiving documents");
			return null;
		}
		
		return foundDocuments;
	}
	
	/* 
	 * Searches for documents 
	 * based on how overdue it is
	 * and returns the results 
	 */
	static private PreparedStatement getDueDocumentsStatement;
	static private final String GET_DUE_DOCUMENTS_STATEMENT = "SELECT " + DOC_QUERY_COMPONENTS + " FROM Documents WHERE DepartmentName = ? AND Assignee IS Null AND UpdateInterval > '0' AND (DATEDIFF('day', EffectiveDate, CURDATE()) > ((UpdateInterval - ?)* 30.4375) OR EffectiveDate = null);";
	static public List<Document> searchDueDocuments(User u, int monthsOverDue) {
		List<Document> foundDocuments = new ArrayList<Document>();
		try {
			getDueDocumentsStatement.setString(1, u.getDepartmentName());
			getDueDocumentsStatement.setInt(2, monthsOverDue);
			ResultSet rs = getDueDocumentsStatement.executeQuery();
			while(rs.next())
				foundDocuments.add(constructDocument(rs));
			for(Document d : foundDocuments)
				d.setProperties(getDocumentProperties(d.getDocumentID()));
		}
		catch (SQLException sql) {
			Main.printStackTrace(sql, "Error retreiving documents");
			return null;
		}
		return foundDocuments;
	}



	static public List<String[]> getTableDimensions(String item, String type) {
		try {
			List<String[]> resultList = new ArrayList<String[]>();
			ResultSet rs = query("SELECT Index, "+type+"Value FROM TableDimensions WHERE Item = '"+ item +"';");
			while(rs.next()) { 
				resultList.add(new String[] {rs.getString(1), rs.getString(2)});
			}
			return resultList;
		}
		catch(SQLException ex) {
			Main.printStackTrace(ex, "Error occurred trying to get Categories");
		}
		return null;
	}
	
	static public User getUser(String userName) {
		try {
			PreparedStatement pstmt = con.prepareStatement("SELECT UserName, FirstName, LastName, DepartmentName, IsDatabaseAdmin, IsDepartmentAdmin FROM Users WHERE UserName = ?;");
			pstmt.setString(1, userName);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) { 
				return new User(
					rs.getString(1),
					rs.getString(2),
					rs.getString(3),
					rs.getString(4),
					rs.getBoolean(5),
					rs.getBoolean(6));
			}
		}
		catch (SQLException e) {
			Main.printStackTrace(e, "Error retrieving information for user " + userName);
		}
		return null;
	}
	
	static public boolean getUserIsAdmin(String user) {
		User u = getUser(user);
		if (u != null)
			return u.isAdmin();
		return false;
	}
	
	static public boolean getUserIsAdmin(User a) {
		return getUserIsAdmin(a.getUserName()) && a.isAdmin();
	}
	
	static public List<Document> getAllDocuments() throws SQLException {
		List<Document> resultElements = new ArrayList<Document>();
		PreparedStatement pstmt = con.prepareStatement("SELECT " + DOC_QUERY_COMPONENTS + " FROM Documents;");
		ResultSet rs = pstmt.executeQuery();
		String docActiveXCall;
		while(rs.next())
			resultElements.add(constructDocument(rs));
		for(Document doc : resultElements)
			doc.setProperties(getDocumentProperties(doc.getDocumentID()));
		
		return resultElements;
	}
	
	static public ResultSet getAllDocumentsResultSet() throws SQLException {
		PreparedStatement pstmt = con.prepareStatement("SELECT " + DOC_QUERY_COMPONENTS + " FROM Documents;");
		return(pstmt.executeQuery());
	}
	
	static public List<User> getUsers(String departmentName) throws SQLException {
		List<User> resultElements = new ArrayList<User>();
		ResultSet rs;
		if (departmentName == null) 
			rs = DBController.query("SELECT UserName, FirstName, LastName, DepartmentName, IsDatabaseAdmin, IsDepartmentAdmin FROM Users;");
		else
			rs = DBController.query("SELECT UserName, FirstName, LastName, DepartmentName, IsDatabaseAdmin, IsDepartmentAdmin FROM Users WHERE DepartmentName = '" + departmentName + "';");
		while(rs.next()) { 
			resultElements.add(new User(
				rs.getString(1),
				rs.getString(2),
				rs.getString(3),
				rs.getString(4),
				rs.getBoolean(5),
				rs.getBoolean(6))
			);
		}
		return resultElements;
	}
	
	static private PreparedStatement updateDocumentPathStatement;
	static private final String UPDATE_DOCUMENT_PATH_STATEMENT = "UPDATE Documents SET DocumentPath = ? WHERE DocumentID = ?;";
	static public boolean updateDocumentPath(Document d, File f) {
		try {
			if (!f.exists() && !Dialogs.showAlertConfirm(d, "Path to this file may not exist. Continue?"))
					return false;
				
			File fRoot = new File(Utils.getDocRoot());
			String newPath = fRoot.toURI().relativize(f.getParentFile().toURI()).getPath();
			Main.printDebug("Updating DB document path: " + fRoot.toURI() + " to: " + f.toURI());
			
			updateDocumentPathStatement.setString(1, newPath);
			updateDocumentPathStatement.setInt(2, d.getDocumentID());
			boolean success = (updateDocumentPathStatement.executeUpdate() == 1);
			Main.printDebug("Update " + (success ? "succeeded" : "failed"));
			return success;
		}
		catch (SQLException sql) {
			Main.printStackTrace(sql, "Error updating path");
		}
		return false;
	}
	
	static private PreparedStatement setDocumentApprovedStatement;
	static private final String SET_DOCUMENT_APPROVED_STATEMENT = "UPDATE Documents SET IsApproved = ? WHERE DocumentID = ?;";
	static public boolean setDocumentApproved(Document d) {
		try {
			setDocumentApprovedStatement.setBoolean(1, true);
			setDocumentApprovedStatement.setInt(2, d.getDocumentID());
			return setDocumentApprovedStatement.executeUpdate() == 1;
		}
		catch (SQLException sql) {
			Main.printStackTrace(sql, "Error setting approved");
		}
		return false;
	}
	
	
	static private PreparedStatement updateDocumentDateStatement;
	static private final String UPDATE_DOCUMENT_DATE_STATEMENT = "UPDATE Documents SET EffectiveDate = NOW() WHERE DocumentID = ?;";
	static public boolean updateDocumentDate(Document d) {
		try {
			updateDocumentDateStatement.setInt(1, d.getDocumentID());
			return updateDocumentDateStatement.executeUpdate() == 1;
		}
		catch (SQLException sql) {
			Main.printStackTrace(sql, "Error updating date");
		}
		return false;
	}
	
	static private PreparedStatement assignDocumentStatement;
	static private final String ASSIGN_DOCUMENT_STATEMENT = "UPDATE Documents SET Assignee = ? WHERE DocumentID = ?;";
	static public boolean assignDocument(Document d, User u) {
		try {
			String userName;
			if (u == null)
				userName = null;
			else
				userName = u.getUserName();
			assignDocumentStatement.setString(1, userName);
			assignDocumentStatement.setInt(2, d.getDocumentID());
			return (assignDocumentStatement.executeUpdate() == 1);
		}
		catch (SQLException sql) {
			Main.printStackTrace(sql, "Error assigning document");
		}
		return false;
	}
	
	static private PreparedStatement getAssignedDocumentsStatement;
	static private final String GET_ASSIGNED_DOCUMENTS_STATEMENT = "SELECT " + DOC_QUERY_COMPONENTS + " FROM Documents WHERE DepartmentName = ? AND Assignee IS NOT NULL;";
	static public List<Document> getAssignedDocuments(String departmentName) {
		try {
			getAssignedDocumentsStatement.setString(1, departmentName);
			ResultSet rs = getAssignedDocumentsStatement.executeQuery();
			List<Document> docList = new ArrayList<Document>();
			while (rs.next()) {
				docList.add(
					new Document(
						rs.getInt(1), 
						rs.getString(2),
						rs.getString(3),
						rs.getString(4),
						rs.getString(5),
						rs.getDate(6), 
						rs.getInt(7),
						rs.getString(8),
						rs.getBoolean(9),
						rs.getString(10), 
						rs.getString(11), 
						(String)getExtensionSingleProperty("ActiveX", Utils.getExtension(rs.getString(3)))
					)
				);
			}
			for(Document d : docList)
				d.setProperties(getDocumentProperties(d.getDocumentID()));
			
			return docList;
		}
		catch (SQLException sql) {
			Main.printStackTrace(sql, "Error getting assigned documents");
		}
		return null;
	}
	
	static public List<String> getAllDepartments() throws SQLException {
		List<String> resultElements = new ArrayList<String>();
		ResultSet rs = DBController.query("SELECT DepartmentName from Departments;");
		while(rs.next())
			resultElements.add(rs.getString(1));
		return resultElements;
	}
	
	static public DocumentProperties getDocumentProperties(int documentID) throws SQLException {
		PreparedStatement pstmt = con.prepareStatement("SELECT PropertyName, PropertyValue FROM DocumentProperties WHERE DocumentID = ?;");
		pstmt.setInt(1, documentID);
		ResultSet rs = pstmt.executeQuery();
		DocumentProperties properties = new DocumentProperties();
		while(rs.next()) {
			properties.setProperty(rs.getString(1), rs.getString(2));
		}
		properties.lockProperties();
		return properties;
	}
	
	static private String getDepartmentProperty(String departmentName, String propertyVal) {
		try {
			PreparedStatement pstmt = con.prepareStatement("SELECT StaticValue FROM DepartmentProperties WHERE DepartmentName = ? AND PropertyName = ?;");
			pstmt.setString(1, departmentName);
			pstmt.setString(2, propertyVal);
			ResultSet rs = pstmt.executeQuery();
			if(rs.next()) {
				return rs.getString(1);
			}
		}
		catch(SQLException ex) {
			Main.printStackTrace(ex, "Error occurred trying to get " + propertyVal + " for: " + departmentName);
		}
		return "Unknown";
	}
	
	static public String getManager(String departmentName) {
		return getDepartmentProperty(departmentName, "Manager");
	}
	
	static public String getCompany(String departmentName) {
		return getDepartmentProperty(departmentName, "Company");
	}
	
	/*
	 * Gets a list of just the names of the properties this department supports.
	 */
	static public List<String> getDepartmentProperties(String departmentName) throws SQLException {
		List<String> properties = new ArrayList<String>();
		
		PreparedStatement pstmt = con.prepareStatement("SELECT PropertyName FROM DepartmentProperties WHERE DepartmentName = ?;");
		pstmt.setString(1, departmentName);
		ResultSet rs = pstmt.executeQuery();
		
		//ResultSet rs = query("SELECT PropertyName FROM DepartmentProperties WHERE DepartmentName = '" + departmentName + "';");
		while(rs.next()) {
			properties.add(rs.getString(1));
		}
		return properties;
	}
	
	/*
	 * Returns all info concerning department properties.
	 */
	static public DepartmentProperties getDepartmentPropertiesFull(String departmentName) throws SQLException {
		DepartmentProperties properties = new DepartmentProperties();
		ResultSet rs = query("SELECT PropertyName, IsStatic, Locked, StaticValue, ShowInHeader, PositionInHeader, ShowInProperties "
							+ "FROM Properties AS Props JOIN DepartmentProperties AS DProps "
							+ "ON DProps.DepartmentName = '" + departmentName + "' AND DProps.PropertyName = Props.PropertyName;");
		
		while(rs.next()) {
			DepartmentProperties.Property p = properties.createNewProperty();
			p.set(DepartmentProperties.PropertyID.PROPERTY_NAME, rs.getString(1));
			p.set(DepartmentProperties.PropertyID.IS_STATIC, rs.getBoolean(2));
			p.set(DepartmentProperties.PropertyID.LOCKED, rs.getBoolean(3));
			p.set(DepartmentProperties.PropertyID.STATIC_VALUE, rs.getString(4));
			p.set(DepartmentProperties.PropertyID.SHOW_IN_HEADER, rs.getBoolean(5));
			p.set(DepartmentProperties.PropertyID.POSITION_IN_HEADER, rs.getInt(6));
			p.set(DepartmentProperties.PropertyID.SHOW_IN_PROPERTIES, rs.getBoolean(7));
			p.lock();
			
			properties.setProperty((String)p.get(DepartmentProperties.PropertyID.PROPERTY_NAME), p);
		}
		properties.lock();
		return properties;
	}
	
	/*
	 * Adds a new property to a Department.
	 */
	static private PreparedStatement addDepartmentPropertyStatement;
	static private final String ADD_DEPARTMENT_PROPERTY_STATEMENT = "INSERT INTO DepartmentProperties (DepartmentName, PropertyName, StaticValue, ShowInHeader, PositionInHeader, ShowInProperties) VALUES (?, ?, null, false, -1, false);";
	static public boolean addDepartmentProperty(String departmentName, String propertyName) throws SQLException {
		addProperty(propertyName);
		addDepartmentPropertyStatement.setString(1, departmentName);
		addDepartmentPropertyStatement.setString(2, propertyName);
		return addDepartmentPropertyStatement.executeUpdate() == 1;
	}
	
	/*
	 * Adds a property to the Properties table if it is not there already.
	 * Sets the property to have IsStatic=TRUE and Locked=FALSE.
	 */
	static private PreparedStatement addPropertyStatement;
	static private final String ADD_PROPERTY_STATEMENT = "INSERT INTO Properties (PropertyName, IsStatic, Locked) VALUES (?, TRUE, FALSE);";
	static private boolean addProperty(String propertyName) throws SQLException {
		addPropertyStatement.setString(1, propertyName);
		return addPropertyStatement.executeUpdate() == 1;
	}
	
	static private PreparedStatement deleteDepartmentPropertyStatement;
	static private final String DELETE_DEPARTMENT_PROPERTY_STATEMENT = "DELETE FROM DepartmentProperties WHERE DepartmentName = ? AND PropertyName = ?;";
	static public boolean deleteDepartmentProperty(String departmentName, String propertyName) throws SQLException {
		deleteDepartmentPropertyStatement.setString(1, departmentName);
		deleteDepartmentPropertyStatement.setString(2, propertyName);
		return deleteDepartmentPropertyStatement.executeUpdate() == 1;
	}
	
	/*
	 * Returns a list of global property names.
	 */
	static private List<String> getPropertyNames() throws SQLException {
		ResultSet rs = query("SELECT PropertyName FROM Properties;");
		List<String> props = new ArrayList<String>();
		while(rs.next()) {
			String propName = rs.getString(1);
			System.out.println(propName);
			props.add(propName);
		}
		return props;
	}
	
	static public boolean docExistsInDatabase(Object o) {
		return (docQuery(o) != null);
	}
	
	static public boolean isSupportedFile(File i) {
		try {
			if (i.getName().toLowerCase().endsWith(".xml")) return (Utils.getXMLDocumentType(i) != null);
			for (String s : getExtensions()) {
				if (i.getName().endsWith("." + s)) {
					return true;
				}
			}
		}
		catch (Exception e) {}
		return false;
	}
	
	/*
	 * Get list of ActiveX Calls, removing duplicates
	 */
	static public List<String> getActiveXCalls() {
		try {
			HashSet<String> calls = new HashSet<String>();
			List<String> returnedCalls = new ArrayList<String>();
			ResultSet rs = query("SELECT ActiveX FROM Extensions;");
			while(rs.next()) {
				calls.add(rs.getString(1));
			}
			for (String w : calls)
				returnedCalls.add(w);
			return returnedCalls;
		}
		catch(SQLException e) {
			Main.printStackTrace(e, "Error occurred trying to get extension names");
		}
		return null;
	}
	
	static public boolean isSupportedExtension(String s) {
		return isSupportedFile(new File("." + s));
	}
	
	static public Document docQuery(Object o) {
		try {
			ResultSet rs;
			PreparedStatement pstmt;
			if (o instanceof Integer) {
				pstmt = con.prepareStatement("SELECT " + DOC_QUERY_COMPONENTS + " FROM Documents WHERE DocumentID = ?;");
				pstmt.setInt(1, (Integer)o);
			}
			else if (o instanceof String || o instanceof File || o instanceof Document) {
				pstmt = con.prepareStatement("SELECT " + DOC_QUERY_COMPONENTS + " FROM Documents WHERE (DocumentName = ? OR NewDocumentName = ?) AND Extension = ?;");
				pstmt.setString(1, Utils.getSubject(o));
				pstmt.setString(2, Utils.getSubject(o));
				pstmt.setString(3, Utils.getExtension(o));
			}
			else
				return null;
			rs = pstmt.executeQuery();
			if(!rs.next())
				return null;
			return constructDocument(rs);
		}
		catch (SQLException sql) {
			Main.printStackTrace(sql, "Error retreiving document");
			
		}
		return null;
	}
	
	/*
	 * Executes an SQL query and returns the ResultSet unaltered.
	 *
	 * Does not check to see if there is data in the table. You must
	 * check with rs.next() before accessing.
	 *
	 * If you know there are multiple rows, you'll want to use a
	 * while loop, ie:
	 * 		while(rs.next()) {
	 *			// do stuff
	 *		}
	 *
	 * Never returns null. Will throw SQLException if it receives
	 * an invalid query statement.
	 */
	static private ResultSet query(String sqlQuery) throws SQLException {
		return s.executeQuery(sqlQuery);
	}
	
	/*
	 * Executes an SQL query and returns the ResultSet after
	 * performing a ResultSet.next().
	 *
	 * This is good for queries you know to only return
	 * one row of data, as this method will return the
	 * ResultSet ready to read from the first row,
	 * or null if there is no data at all to be read.
	 */
	static private ResultSet queryQuick(String sqlQuery) throws SQLException {
		ResultSet rs = query(sqlQuery);
		if(rs.next()) {
			return rs;
		}
		//Main.printDebug("No data returned from statement: " + sqlQuery);
		return null;
	}
	
	/*
	 * executes an sql update.
	 * returns the updated row count, or 0 in the case that the statement returns nothing.
	 * SQLException thrown if given statement would produce a ResultSet.
	 */
	static private int update(String sqlUpdate) throws SQLException {
		return s.executeUpdate(sqlUpdate);
	}
	
	/*
	 * Encrypts the given String. Returns null if an error occurs.
	 */
	static private String encryptString(String s) {
		try {
			MessageDigest md = MessageDigest.getInstance("SHA-1");
			md.update(s.getBytes());
			return Base64.encodeObject(md.digest());
		}
		catch(NoSuchAlgorithmException ex) {
			Main.printStackTrace(ex, "Encryption algorithm not found.");
			return null;
		}
	}
	
	static private void prepareStatements() throws SQLException {
		isFirstLoginStatement = con.prepareStatement(IS_FIRST_LOGIN_STATEMENT);
		checkPasswordStatement = con.prepareStatement(CHECK_PASSWORD_STATEMENT);
		updateUserStatement = con.prepareStatement(UPDATE_USER_STATEMENT);
		addUserStatement = con.prepareStatement(ADD_USER_STATEMENT);
		updateExtensionStatement = con.prepareStatement(UPDATE_EXTENSION_STATEMENT);
		addExtensionStatement = con.prepareStatement(ADD_EXTENSION_STATEMENT);
		deleteExtensionStatement = con.prepareStatement(DELETE_EXTENSION_STATEMENT);
		deleteUserStatement = con.prepareStatement(DELETE_USER_STATEMENT);
		addDepartmentPropertyStatement = con.prepareStatement(ADD_DEPARTMENT_PROPERTY_STATEMENT);
		addPropertyStatement = con.prepareStatement(ADD_PROPERTY_STATEMENT);
		deleteDepartmentPropertyStatement = con.prepareStatement(DELETE_DEPARTMENT_PROPERTY_STATEMENT);
		assignDocumentStatement = con.prepareStatement(ASSIGN_DOCUMENT_STATEMENT);
		getAssignedDocumentsStatement = con.prepareStatement(GET_ASSIGNED_DOCUMENTS_STATEMENT);
		getDueDocumentsStatement = con.prepareStatement(GET_DUE_DOCUMENTS_STATEMENT);
		updateDocumentPathStatement = con.prepareStatement(UPDATE_DOCUMENT_PATH_STATEMENT);
		updateDocumentDateStatement = con.prepareStatement(UPDATE_DOCUMENT_DATE_STATEMENT);
		setDocumentApprovedStatement = con.prepareStatement(SET_DOCUMENT_APPROVED_STATEMENT);
		addFileServerStatement = con.prepareStatement(ADD_FILESERVER_STATEMENT);
		removeFileServerStatement = con.prepareStatement(REMOVE_FILESERVER_STATEMENT);
		addCategoryStatement = con.prepareStatement(ADD_CATEGORY_STATEMENT);
		deleteCategoryStatement = con.prepareStatement(DELETE_CATEGORY_STATEMENT);
		updateCategoryStatement = con.prepareStatement(UPDATE_CATEGORY_STATEMENT);
		addDocumentStatement = con.prepareStatement(ADD_DOCUMENT_STATEMENT);
		removeAllDocumentPropertiesStatement = con.prepareStatement(REMOVE_ALL_DOCUMENT_PROPERTIES_STATEMENT);
		removeAllDocumentsStatement = con.prepareStatement(REMOVE_ALL_DOCUMENTS_STATEMENT);
		addDocumentPropertiesStatement = con.prepareStatement(ADD_DOCUMENT_PROPERTIES_STATEMENT);
	}
		
	static public boolean connectToDatabase(String driver, String url, String name, String pw) throws SQLException {
		if(!isConnected) {
			/*
			 * Establish connection to our HSQL database
			 */
			try {
				//Class.forName("org.hsqldb.jdbcDriver");
                                Class.forName(driver);
				//String database = Main.sysIni.getStringProperty("HSQL Database", "url");
                                //String database = Main.sysIni.getStringProperty("HSQL Database", "url");
				con = DriverManager.getConnection(url, name, pw);
				s = con.createStatement();
				prepareStatements();
				// successfully connected.
				isConnected = true;
			}
			catch(SQLException sql) {
				Dialogs.showError(sql, "SQL Error loading program");
			}
			catch(ClassNotFoundException cnf) {
				Dialogs.showError(cnf, "HSQL JDBC driver not found");
			}
		}
		
		return isConnected;
	}
	
	static public boolean createInternalDatabase() throws Exception { //throws SQLException {
		if(!isConnected) {
			//try {
				//if (!Utils.copyJarFolder(".db/data", "dbdata"))
				//	throw new IOException("Could not copy test database data");
				if (!Utils.copyJarFolder("docdb.jar", "data")) {
					throw new IOException("Could not extract sample data!");
                                }
                                
                                File f1 = new File("database/data/docdb.script");
                                File f2 = new File("database/data/docdb.properties");
                                
                                if (!f1.exists() || !f2.exists()) {
                                        throw new SQLException("Can't start server!  Reason: Could not extract sample data!");
                                }
				/* 
				 * Set up embedded server stuff
				 */
				embeddedServer = new Server();
				String serverProps = "database.0=database/data/docdb;dbname.0=docdb";
				String url = "jdbc:hsqldb:hsql://localhost/docdb";
				
				embeddedServer.putPropertiesFromString(serverProps);
				embeddedServer.setLogWriter(null);
             			embeddedServer.setErrWriter(null);
             			embeddedServer.start();
				
				Class.forName("org.hsqldb.jdbcDriver");
				con = DriverManager.getConnection(url, "SA", "");
				s = con.createStatement();
				prepareStatements();
				/*
				 * Successfully connected
				 */
				isConnected = true;
                       // }
			
		}
		return isConnected;
	}
	
	static public void stopInternalDatabase() {
		if (embeddedServer != null) {
			embeddedServer.stop();
			embeddedServer = null;
		}	
	}
	
	/* 
	 * Constructs a document based on a ResultSet 
	 * that's already formatted based on DOC_QUERY_COMPONENTS 
	 */
	public static Document constructDocument(ResultSet rs) throws SQLException { 
		String rsActiveX = (String)getExtensionSingleProperty("ActiveX", Utils.getExtension(rs.getString(3)));
		return new Document(rs.getInt(1), 
			rs.getString(2),
			rs.getString(3),
			rs.getString(4),
			rs.getString(5),
			rs.getDate(6), 
			rs.getInt(7),
			rs.getString(8),
			rs.getBoolean(9),
			rs.getString(10), 
			rs.getString(11), 
			rsActiveX);
	}
	
	static public void close() {
		if(isConnected) {
			isConnected = false;
			try {
				con.close();
			}
			catch(SQLException ex) {
				Main.printStackTrace(ex, "HSQL Database error on close");
			}
		}
	}
}
